﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class DbAccess
    {
               //encapsulated variables
        private SqlConnection _myConn;
        private SqlCommand _myComand;

        //constructor
        public DbAccess()
        { 
            //initializing connection and command variables

            _myConn = new SqlConnection("server=(local);database=TravelExperts;integrated security=SSPI");
            _myComand = new SqlCommand();
            _myComand.Connection = _myConn;
        }

        //retriving list of Packages objects from the database in a list collection


        public List<Packages> SelectPackages()
        {
            List<Packages> myPackages = new List<Packages>();

            //set the command text
            _myComand.CommandText = "Select * from Packages";

            try
            {
                _myConn.Open();
                SqlDataReader pkgReader = _myComand.ExecuteReader();

                while (pkgReader.Read())
                {
                    //initializing products object

                    Packages objPackages = new Packages();
                    
                    objPackages.PackageId = pkgReader.GetInt32(0);
                    objPackages.PkgName = pkgReader.GetString(1);
                    objPackages.PkgStartDate =pkgReader.GetDateTime(2);
                    objPackages.PkgEndDate = pkgReader.GetDateTime(3);
                    objPackages.PkgDesc =pkgReader.GetString(4) ;
                    objPackages.PkgBasePrice =pkgReader.GetDecimal(5) ;
                    objPackages.PkgAgencyCommission =pkgReader.GetDecimal(6) ;
                    myPackages.Add(objPackages);

                }
                pkgReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _myConn.Close();
            }


            return myPackages;
        }

        public List<PackagesProductsSupplier> SelectPackagesProSupp()
        {
            List<PackagesProductsSupplier> myPkgProSup = new List<PackagesProductsSupplier>();

            //set the command text
            _myComand.CommandText = "select * from Packages_Products_Suppliers ";

            try
            {
                _myConn.Open();
                SqlDataReader pkgReader = _myComand.ExecuteReader();

                while (pkgReader.Read())
                {
                    //initializing  object

                    PackagesProductsSupplier objPackages = new PackagesProductsSupplier();
                    

                    objPackages.PackageId = pkgReader.GetInt32(0);
                    objPackages.ProductSupplierId = pkgReader.GetInt32(1);
                   
                    myPkgProSup.Add(objPackages);

                }
                pkgReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _myConn.Close();
            }


            return myPkgProSup;
        }

        public List<Products> SelectProducts()
        {
            List<Products> myPkgProSup = new List<Products>();

            //set the command text
            _myComand.CommandText = "select * from Products ";

            try
            {
                _myConn.Open();
                SqlDataReader proReader = _myComand.ExecuteReader();

                while (proReader.Read())
                {
                    //initializing  object

                    Products objProducts = new Products();

                    
                    objProducts.ProductId = proReader.GetInt32(0);
                    objProducts.ProdName = proReader.GetString(1);

                    myPkgProSup.Add(objProducts);

                }
                proReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _myConn.Close();
            }


            return myPkgProSup;
        }

        public List<Suppliers> SelectSuppliers()
        {
            List<Suppliers> mySuppliers = new List<Suppliers>();

            //set the command text
            _myComand.CommandText = "select * from Suppliers";

            try
            {
                _myConn.Open();
                SqlDataReader supReader = _myComand.ExecuteReader();

                while (supReader.Read())
                {
                    //initializing  object

                    Suppliers objSuppliers = new Suppliers();

                    
                    objSuppliers.SupplierId = supReader.GetInt32(0);
                    objSuppliers.SupName = supReader.GetString(1);

                    mySuppliers.Add(objSuppliers);

                }
                supReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _myConn.Close();
            }


            return mySuppliers;
        }

        public List<ProductsSuppliers> SelectProductsSuppliers()
        {
            List<ProductsSuppliers> myProSuppliers = new List<ProductsSuppliers>();

            //set the command text
            _myComand.CommandText = "select * from Products_suppliers";

            try
            {
                _myConn.Open();
                SqlDataReader proSupReader = _myComand.ExecuteReader();

                while (proSupReader.Read())
                {
                    //initializing  object

                    ProductsSuppliers objSuppliers = new ProductsSuppliers();

                    objSuppliers.ProductSupplierId = proSupReader.GetInt32(0);
                    objSuppliers.ProductId = proSupReader.GetInt32(1);
                    objSuppliers.SupplierId = proSupReader.GetInt32(2);

                    myProSuppliers.Add(objSuppliers);

                }
                proSupReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _myConn.Close();
            }


            return myProSuppliers;
        }

         /*1) DATABASE functions for PACKAGES*/

        //Insert a new Package object into the database

        public void InsertPackages(Packages myPkg)
        {
            _myComand.CommandText = "INSERT INTO Packages VALUES (@PkgName,@PkgStartDate,@PkgEndDate,@PkgDesc,@PkgBasePrice,@PkgAgencyCommission)";
            ExecuteNonReadPkgs(myPkg);
        }

        public void UpdatePackages(Packages myPkg)
        {
            _myComand.CommandText = "UPDATE Packages SET PkgName=@PkgName,PkgStartDate=@PkgStartDate, PkgEndDate=@PkgEndDate, PkgDesc=@PkgDesc, PkgBasePrice=@PkgBasePrice,PkgAgencyCommission=@PkgAgencyCommission WHERE PackageId=@PackageId";
            ExecuteNonReadPkgs(myPkg);
        }

        public void DeletePackages(Packages mypkg)
        {
            _myComand.CommandText = "DELETE FROM Packages WHERE PackageId=@PackageId";
            ExecuteNonReadPkgs(mypkg);
        }

        private void ExecuteNonReadPkgs(Packages mypkg)
        {
            _myComand.Parameters.AddWithValue("@PackageId", mypkg.PackageId);
            _myComand.Parameters.AddWithValue("@PkgName", mypkg.PkgName);
            _myComand.Parameters.AddWithValue("@PkgStartDate", mypkg.PkgStartDate);
            _myComand.Parameters.AddWithValue("@PkgEndDate", mypkg.PkgEndDate);
            _myComand.Parameters.AddWithValue("@PkgDesc", mypkg.PkgDesc);
            _myComand.Parameters.AddWithValue("@PkgBasePrice", mypkg.PkgBasePrice);
            _myComand.Parameters.AddWithValue("@PkgAgencyCommission", mypkg.PkgAgencyCommission);

            try
            {
                _myConn.Open();
                _myComand.ExecuteNonQuery();
                MessageBox.Show("Congrats, Database updation completed successfully.", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException ex)
            {
                //throw ex;
                MessageBox.Show(ex.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                _myConn.Close();
            }
        }

        /*2) DATABASE functions for Products */

        public void InsertProducts(Products myPro)
        {
            _myComand.CommandText = "INSERT INTO Products VALUES (@ProdName)";
            ExecuteNonReadPro(myPro);
        }

        public void UpdateProducts(Products myPro)
        {
            _myComand.CommandText = "UPDATE Products SET ProdName=@ProdName";
            ExecuteNonReadPro(myPro);
        }

        public void DeleteProducts(Products myPro)
        {
            _myComand.CommandText = "DELETE FROM Products WHERE ProductId=@ProductId";
            ExecuteNonReadPro(myPro);
        }

        private void ExecuteNonReadPro(Products myPro)
        {

            _myComand.Parameters.AddWithValue("@ProductId", myPro.ProductId);
            _myComand.Parameters.AddWithValue("@ProdName", myPro.ProdName);
            

            try
            {
                _myConn.Open();
                _myComand.ExecuteNonQuery();
                MessageBox.Show("Congrats, Database updation completed successfully.", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException ex)
            {
                //throw ex;
                MessageBox.Show(ex.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                _myConn.Close();
            }
        }

        /*3) DATABASE functions for Suppliers */

        public void InsertSuppliers(Suppliers mySup)
        {
            _myComand.CommandText = "INSERT INTO Suppliers VALUES (@SupName)";
            ExecuteNonReadSup(mySup);
        }

        public void UpdateSuppliers(Suppliers mySup)
        {
            _myComand.CommandText = "UPDATE Suppliers SET SupName=@SupName WHERE SupplierId=@SupplierId";
            ExecuteNonReadSup(mySup);
        }

        public void DeleteProducts(Suppliers mySup)
        {
            _myComand.CommandText = "DELETE FROM Suppliers WHERE SupplierId=@SupplierId";
            ExecuteNonReadSup(mySup);
        }

        private void ExecuteNonReadSup(Suppliers mySup)
        {

            _myComand.Parameters.AddWithValue("@SupplierId", mySup.SupplierId);
            _myComand.Parameters.AddWithValue("@SupName", mySup.SupName);


            try
            {
                _myConn.Open();
                _myComand.ExecuteNonQuery();
                MessageBox.Show("Congrats, Database updation completed successfully.", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException ex)
            {
                //throw ex;
                MessageBox.Show(ex.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                _myConn.Close();
            }
        }

        /*4) DATABASE functions for Products_suppliers */
        public void InsertProductsSuppliers(ProductsSuppliers myPs)
        {
            _myComand.CommandText = "INSERT INTO Products_suppliers VALUES (@ProductId,@SupplierId)";
            ExecuteNonReadProSup(myPs);
        }

        public void UpdateProductsSuppliers(ProductsSuppliers myPs)
        {
            _myComand.CommandText = "UPDATE Products_suppliers SET ProductId=@ProductId, SupplierId=@SupplierId  WHERE ProductSupplierId=@ProductSupplierId ";
            ExecuteNonReadProSup(myPs);
        }

        public void DeleteProductsSuppliers(ProductsSuppliers myPs)
        {
            _myComand.CommandText = "DELETE FROM Products_suppliers WHERE ProductSupplierId=@ProductSupplierId";
            ExecuteNonReadProSup(myPs);
        }

        private void ExecuteNonReadProSup(ProductsSuppliers myPs)
        {
            
            _myComand.Parameters.AddWithValue("@SupplierId", myPs.SupplierId);
            _myComand.Parameters.AddWithValue("@ProductId", myPs.ProductId);
            _myComand.Parameters.AddWithValue("@ProductSupplierId", myPs.ProductSupplierId);

            try
            {
                _myConn.Open();
                _myComand.ExecuteNonQuery();
                MessageBox.Show("Congrats, Database updation completed successfully.", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException ex)
            {
                //throw ex;
                MessageBox.Show(ex.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                _myConn.Close();
            }
        }

        /*5) DATABASE functions for Packages_products_suppliers  */

        public void InsertPackagesProductsSuppliers(PackagesProductsSupplier myPkgProSup)
        {
            _myComand.CommandText = "INSERT INTO Packages_products_suppliers VALUES (@PackageId,@ProductSupplierId)";
            ExecuteNonReadPkgProSup(myPkgProSup);
        }

        public void UpdatePackagesProductsSuppliers(PackagesProductsSupplier myPkgProSup)
        {
            /*  ? -2 fields both are PK (may be we need to update 1 field at a time)*/
           // _myComand.CommandText = "UPDATE Packages_products_suppliers SET SupName=@SupName WHERE ProductSupplierId=@ProductSupplierId ";
            //ExecuteNonReadPkgProSup(myPkgProSup);
        }

        public void DeletePackagesProductsSuppliers(PackagesProductsSupplier myPkgProSup)
        {
            //need to pass 2 arguments in DELETE statement because both are PK and both are not unique
            _myComand.CommandText = "DELETE FROM Packages_products_suppliers WHERE ProductSupplierId=@ProductSupplierId and PackageId=@PackageId";
            ExecuteNonReadPkgProSup(myPkgProSup);
        }

        private void ExecuteNonReadPkgProSup(PackagesProductsSupplier myPkgProSup)
        {

            _myComand.Parameters.AddWithValue("@ProductSupplierId", myPkgProSup.ProductSupplierId);
            _myComand.Parameters.AddWithValue("@PackageId", myPkgProSup.PackageId);
            
            try
            {
                _myConn.Open();
                _myComand.ExecuteNonQuery();
                MessageBox.Show("Congrats, Database updation completed successfully.", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException ex)
            {
                //throw ex;
                MessageBox.Show(ex.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                _myConn.Close();
            }
        }

    }
}
